package com.cennavi.bigdata.baotou.dao.impl;

import com.alibaba.fastjson.JSONObject;
import com.cennavi.bigdata.baotou.dao.ImportDao;
import com.cennavi.bigdata.baotou.entities.RoadPieDataNew;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Created by cennavi on 2018/10/23.
 */
@Repository
public class ImportDaoImpl implements ImportDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private JdbcTemplate jdbcTemplatePG;

    @Override
    public JSONObject getRoadId_NameJson() {
        SqlRowSet sqlRowSet = null;
        String sql = "select id,roadname,startname,endname from INDEX_ROAD_INFO ";
        sqlRowSet = jdbcTemplate.queryForRowSet(sql);
        JSONObject jsonObject = new JSONObject();
        while (sqlRowSet.next()){
            String roadid = sqlRowSet.getString("id");
            String roadname = sqlRowSet.getString("roadname");
            String startname = sqlRowSet.getString("startname");
            String endname = sqlRowSet.getString("endname");
            String  roadApplication = roadname + "(" +startname + "," + endname + ")";
            jsonObject.put(roadApplication,roadid);
        }
        return jsonObject;
    }

    @Override
    public int saveRoad_Piecewise_Data(final List<JSONObject> roadList) {

        String  sql = "insert into ROAD_PIECEWISE_DATA (ID,ROADAPPLICATION,ROADDIRECTION,ROADLENGTH,ROADSTARTENDLONT,ROADNAME,KIND，SERIALNUMBER)" +
                " VALUES (?,?,?,?,?,?,?,?)";

        int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                JSONObject jsonObject = roadList.get(i);
                ps.setString(1,jsonObject.getString("roadid"));
                ps.setString(2,jsonObject.getString("roadapplication"));
                ps.setString(3,jsonObject.getString("roaddirection"));
                ps.setDouble(4, Double.valueOf(String.format("%.3f",jsonObject.getDouble("roadlength"))));
                ps.setString(5,jsonObject.getString("roadstartendlont"));
                ps.setString(6,jsonObject.getString("roadName"));
                ps.setString(7,jsonObject.getString("kind"));
                ps.setString(8,jsonObject.getString("serialNumber"));
            }

            @Override
            public int getBatchSize() {
                return roadList.size();
            }
        });
        return ints.length;
    }

    @Override
    public int insertToPgRoadGeomtry(final List<Map<String, Object>> list) {
        String sql = "UPDATE road SET geomtry=? WHERE \"id\"=?";
        int[] ints = jdbcTemplatePG.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> map = list.get(i);
                ps.setString(1, map.get("geomtry").toString());
                ps.setString(2, map.get("ID").toString());
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });
        return ints.length ;
    }

    @Override
    public int saveRoadPieNewList(final List<RoadPieDataNew> roadPieNewList) {
        String sql = "insert into road_piecewise_data_new_t (" +
                "ROADID,ROADNAME,ROADDIRECTION,ROADLENGTH,MAXTRAVELTIME,MINTRAVELTIME,KIND" +
                ") values (?,?,?,?,?,?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {

                RoadPieDataNew roadPieDataNew = roadPieNewList.get(i);
                ps.setString(1, roadPieDataNew.getROADID());
                ps.setString(2, roadPieDataNew.getROADNAME());
                ps.setString(3, roadPieDataNew.getROADDIRECTION());
                ps.setDouble(4, roadPieDataNew.getROADLENGTH());
                ps.setDouble(5, roadPieDataNew.getMAXTRAVELTIME());
                ps.setDouble(6, roadPieDataNew.getMINTRAVELTIME());
                ps.setString(7, roadPieDataNew.getKIND());
            }

            @Override
            public int getBatchSize() {
                return roadPieNewList.size();
            }
        });
        return ints.length;
    }

    @Override
    public int saveRoadids(final List<Map<String, Object>> roadids) {

        String sql = "insert into SWAN_ROAD_PIECEWISE_MIDDLE  values (?,?)";
        int[] ints = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Map<String, Object> map = roadids.get(i);
                ps.setString(1, map.get("id").toString());
                ps.setString(2, map.get("roadid").toString());
            }
            @Override
            public int getBatchSize() {
                return roadids.size();
            }
        });
        return ints.length;
    }
}
